package com.xs.ahoi.hcxs.dao;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import com.xs.ahoi.hcxs.model.Catalog;

import java.util.ArrayList;
import java.util.List;

/**
 * author: ahoi
 * date: 2018/6/21 13:34
 * description:
 */
public class CatalogDao {

    private static CatalogDao instance;
    private static SQLiteDatabase db;

    private CatalogDao() {
    }

    public static synchronized CatalogDao getInstance() {
        if (db == null) {
            db = DBHelper.getInstance().getDb();
        }
        if (instance == null) {
            instance = new CatalogDao();
        }
        return instance;
    }

    /**
     * 保存章节目录
     */
    public void save(int bookId, List<Catalog> catalogs) {

        // 开始事务
        db.beginTransaction();

        try {
            // 先删除目录
            db.execSQL("delete from " + DBHelper.TABLE_NAME_CATALOG + " where book_id = ?", new Object[]{bookId});

            // 批量插入目录
            for (Catalog category : catalogs) {
                db.execSQL("insert into " + DBHelper.TABLE_NAME_CATALOG + "(book_id, name, position) values (?, ?, ?)",
                        new Object[]{bookId, category.getName(), category.getPosition()});
            }

            // 设置事务处理成功，不设置会自动回滚不提交
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 提交事务
            db.endTransaction();
        }
    }

    /**
     * 获取章节目录
     */
    public List<Catalog> listByBookId(int bookId) {
        List<Catalog> catalogs = new ArrayList<>();
        Cursor cursor = null;
        String sql = "select * from " + DBHelper.TABLE_NAME_CATALOG + " where book_id = ?";

        try {
            cursor = db.rawQuery(sql, new String[]{bookId + ""});
            while (cursor.moveToNext()) {
                catalogs.add(cursorToEntity(cursor));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }

        return catalogs;
    }

    /**
     * 获取页面当前位置的标题
     */
    public String getNameByPosition(int bookId, int position) {
        String sql = "select name from " + DBHelper.TABLE_NAME_CATALOG + " where book_id = ? and position <= ? order by position desc limit 1";
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, new String[]{bookId + "", position + ""});
            if (cursor.moveToNext()) {
                return cursor.getString(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return "";
    }

    /**
     * 获取页面当前位置的下一章的位置坐标
     */
    public Integer getNextPosition(int bookId) {
        String sql = "select position from " + DBHelper.TABLE_NAME_CATALOG + " where book_id = ? and position > (select last_read_position from book where id = ?) order by position asc limit 1";
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, new String[]{bookId + "", bookId + ""});
            if (cursor.moveToNext()) {
                return cursor.getInt(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return null;
    }

    /**
     * 获取页面当前位置的上一章的位置坐标
     */
    public Integer getPrevPosition(int bookId) {
        String sql = "select position from " + DBHelper.TABLE_NAME_CATALOG + " where book_id = ? and position < (select last_read_position from book where id = ?) order by position desc limit 1";
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, new String[]{bookId + "", bookId + ""});
            if (cursor.moveToNext()) {
                return cursor.getInt(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return null;
    }

    /**
     * 删除目录
     */
    public void deleteByBookId(String bookId) {
        String sql = "delete from " + DBHelper.TABLE_NAME_CATALOG + " where book_id in (" + bookId + ")";
        db.execSQL(sql);
    }

    /**
     * 获取页面当前位置的标题
     */
    public Catalog getCurrentOneByBookId(int bookId) {
        String sql = "select * from " + DBHelper.TABLE_NAME_CATALOG + " where book_id = " + bookId + " and position <= " +
                " (select last_read_position from " + DBHelper.TABLE_NAME_BOOK + " where id = " + bookId + ") order by position desc limit 1";
        Catalog catalog = null;
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                catalog = cursorToEntity(cursor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return catalog;
    }

    private Catalog cursorToEntity(Cursor cursor) {
        Catalog catalog = new Catalog();
        catalog.setId(cursor.getInt(0));
        catalog.setBookId(cursor.getInt(1));
        catalog.setName(cursor.getString(2));
        catalog.setPosition(cursor.getInt(3));
        return catalog;
    }

}
